package com.aaa.project.zhreport.service.impl;

import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import com.aaa.common.exception.ServiceException;
import com.aaa.project.zhreport.domain.YwfxDataSources;
import com.aaa.project.zhreport.service.ExcelParseService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author baibin
 * @version 1.0
 * @date 2025/5/12 14:11
 * @description ExcelParseServiceImpl
 */
@Service
public class ExcelParseServiceImpl implements ExcelParseService {

    private static final Logger log = LoggerFactory.getLogger(ExcelParseServiceImpl.class);

    @Resource(name = "billOracleDataSource")
    private DataSource billOracleDataSource;
    @Resource(name = "authOracleDataSource")
    private DataSource authOracleDataSource;
    @Resource(name = "billPGDataSource")
    private DataSource billPGDataSource;

    @Override
    @Async
    public void parseExcelAndGenerateTable(String tableName, YwfxDataSources dataSources) {
        List<List<String>> rows = new ArrayList<>();
        List<String> headerRow = Arrays.asList("user_id","column2","column3","column4","column5","column6","column7","column8","column9","column10");
        rows.add(headerRow);
        rows.add(Arrays.asList("","","","","","","","","",""));
        // 生成 DDL 语句
        StringBuilder ddl = new StringBuilder("CREATE TABLE " + tableName + " (");
        // 解析表头生成字段定义
        for (int i = 0; i < headerRow.size(); i++) {
            Object headerCell = headerRow.get(i);
            String columnName = StrUtil.toString(headerCell);
            if (StrUtil.isBlank(columnName)) {
                continue; // 跳过空列
            }
            // 判断 columnName 是否包含中文
            if (containsChinese(columnName)) {
                throw new ServiceException("表头列名不能包含中文：" + columnName);
            }

            // 生成字段定义
            String fieldType = "VARCHAR(128)"; // 默认类型为字符串
            ddl.append(columnName).append(" ").append(fieldType).append(", ");
        }
        // 移除最后一个逗号并闭合 DDL 语句
        ddl.delete(ddl.length() - 2, ddl.length()).append(")");
        // 根据数据库类型调整 DDL 语句
        String dbType = dataSources.getDataSourceType();
        if ("ORACLE".equalsIgnoreCase(dbType)) {
            ddl = new StringBuilder(ddl.toString().replace("VARCHAR(128)", "VARCHAR2(128)")); // Oracle 使用 VARCHAR2
        }
        // 执行 DDL 语句创建临时表
        executeDDL(ddl.toString(), dataSources);
        log.info("create table {} success！", tableName);
        // 构建批量插入的 SQL 语句
        for (int i = 1; i < rows.size(); i++) {
            // 跳过第一行表头
            List<String> rowData = rows.get(i);
            StringBuilder dml = new StringBuilder("INSERT INTO " + tableName + " VALUES (");
            for (String cell : rowData) {
                dml.append("'").append(cell).append("', ");
            }
            // 移除最后一个逗号并闭合 DML 语句
            dml.delete(dml.length() - 2, dml.length()).append(")");
            // 执行 DML 语句插入数据
            executeDML(dml.toString(), dataSources);
        }
        log.info("insert {} data success！", tableName);
    }

    public void parseExcelAndGenerateTable(List<List<Object>> rows, String tableName, YwfxDataSources dataSources) {
        // 第一行是表头
      /*  List<Object> headerRow = rows.get(0);
        // 生成 DDL 语句
        StringBuilder ddl = new StringBuilder("CREATE TABLE " + tableName + " (");
        // 解析表头生成字段定义
        for (int i = 0; i < headerRow.size(); i++) {
            Object headerCell = headerRow.get(i);
            String columnName = StrUtil.toString(headerCell);
            if (StrUtil.isBlank(columnName)) {
                continue; // 跳过空列
            }
            // 判断 columnName 是否包含中文
            if (containsChinese(columnName)) {
                throw new ServiceException("表头列名不能包含中文：" + columnName);
            }

            // 生成字段定义
            String fieldType = "VARCHAR(128)"; // 默认类型为字符串
            ddl.append(columnName).append(" ").append(fieldType).append(", ");
        }
        // 移除最后一个逗号并闭合 DDL 语句
        ddl.delete(ddl.length() - 2, ddl.length()).append(")");*/
        // 根据数据库类型调整 DDL 语句
//        String dbType = dataSources.getDataSourceType();
//        if ("ORACLE".equalsIgnoreCase(dbType)) {
//            ddl = new StringBuilder(ddl.toString().replace("VARCHAR(128)", "VARCHAR2(128)")); // Oracle 使用 VARCHAR2
//        }
        // 执行 DDL 语句创建临时表
//        executeDDL(ddl.toString(), dataSources);
//        log.info("create table {} success！", tableName);

        // 先将临时表的内容清空
        String truncateStr = "";
        switch (dataSources.getDataSourceType()) {
            case "ORACLE":
                truncateStr = "TRUNCATE TABLE "+ tableName;
                break;
            case "POSTGRESQL":
                truncateStr = "TRUNCATE  "+ tableName;
                break;
        }
        executeDML(truncateStr, dataSources);

        // 构建批量插入的 SQL 语句
        for (int i = 1; i < rows.size(); i++) {
            // 跳过第一行表头
            List<Object> rowData = rows.get(i);
            StringBuilder dml = new StringBuilder("INSERT INTO " + tableName + " VALUES (");
            for (Object cell : rowData) {
                dml.append("'").append( StrUtil.toString(cell)).append("', ");
            }
            // 将为空的字段赋默认值
            if (rowData.size()<9) {
                for (int s = rowData.size(); s < 10; s++) {
                    dml.append("'").append("', ");
                }
            }

            // 移除最后一个逗号并闭合 DML 语句
            dml.delete(dml.length() - 2, dml.length()).append(")");
            // 执行 DML 语句插入数据
            executeDML(dml.toString(), dataSources);
        }
        log.info("insert {} data success！", tableName);
    }

    /**
     * 执行 DDL 语句
     *
     * @param ddl DDL 语句
     * @param dataSources 数据源信息
     */
    @Override
    public void executeDDL(String ddl, YwfxDataSources dataSources) {
        try {
            JdbcTemplate jdbcTemplate = getJdbcTemplate(dataSources);
            jdbcTemplate.execute(ddl);
        } catch (Exception e) {
            String errorMessage = e.getMessage();
            // 如果是 Oracle 错误码 ORA-00942: 表不存在，则忽略
            if (errorMessage.contains("ORA-00942")) {
                log.warn("表不存在，跳过删除");
            } else {
                log.error("执行 DDL 语句失败：{}", ddl, e);
                throw new ServiceException("执行 DDL 语句失败：" + e.getMessage());
            }
        }
    }

    /**
     * 执行 DML 语句
     *
     * @param dml DML 语句
     * @param dataSources 数据源信息
     */
    @Override
    public void executeDML(String dml, YwfxDataSources dataSources) {
        try {
            JdbcTemplate jdbcTemplate = getJdbcTemplate(dataSources);
            jdbcTemplate.execute(dml);
        } catch (Exception e) {
            log.error("执行 DML 语句失败 {}", e.getMessage());
            throw new ServiceException("插入数据失败！");
        }
    }

    /**
     * 根据数据源获取 JdbcTemplate
     *
     * @param dataSources 数据源信息
     * @return JdbcTemplate
     */
    private JdbcTemplate getJdbcTemplate(YwfxDataSources dataSources) {
        JdbcTemplate jdbcTemplate = null;
        switch (dataSources.getDataSourceType()) {
            case "ORACLE":
                // 按类型区分数据库
                if ("BILL".equals(dataSources.getDatabaseName())) {
                    jdbcTemplate = new JdbcTemplate(billOracleDataSource);
                } else if ("RADIUS".equals(dataSources.getDatabaseName())) {
                    jdbcTemplate = new JdbcTemplate(authOracleDataSource);
                }
                break;
            case "POSTGRESQL":
                jdbcTemplate = new JdbcTemplate(billPGDataSource);
                break;
        }
        Assert.notNull(jdbcTemplate, "数据库类型不支持！");
        return jdbcTemplate;
    }

    /**
     * 判断字符串是否包含中文
     *
     * @param str 需要检查的字符串
     * @return boolean 是否包含中文
     */
    private boolean containsChinese(String str) {
        return str.matches(".*[\\u4e00-\\u9fa5].*");
    }
}
